﻿using System;
using System.Data;
using System.Data.DBLib;
using System.Web;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    public class ProjectDB : DBBase
    {
        public ProjectDB()
        {
        }

        public ProjectDB(CSqlDB objDB)
        {
            m_objDB = objDB;
        }

        public DataTable GetInfo(string strProjectGroupID, 
                                    string strProjectCode, 
                                    string strType, 
                                    string strTitle, 
                                    string strCountry,
                                    string strStatus, 
                                    string strApplicant, 
                                    string strInventor, 
                                    string strResponsiblePerson, 
                                    string strAgent,
                                    string strClient, 
                                    string strProjectClass, 
                                    string strDueDateFrom,
                                    string strDueDateTo, 
                                    bool bIsDelete,
                                    string strUserID, 
                                    string strIP)
        {
            if (ConnectDB())
            {
                string strSql = "select *, ISNULL((SELECT TOP 1 ResponsiblePerson FROM ProjectSupervisor  WITH (NOLOCK) WHERE ProjectSupervisor.ProjectCode = Project.ProjectCode ORDER BY CreateDate), N'') as [ResponsiblePerson] from Project where 1 = 1";

                if (!IsEmpty(strProjectGroupID))
                {
                    strSql += " and ProjectGroupID like '%";
                    strSql += strProjectGroupID;
                    strSql += "%'";
                }

                if (!IsEmpty(strProjectCode))
                {
                    strSql += " and ProjectCode like '%";
                    strSql += strProjectCode;
                    strSql += "%'";
                }

                if (!IsEmpty(strType))
                {
                    strSql += " and Type like '%";
                    strSql += strType;
                    strSql += "%'";
                }

                if (!IsEmpty(strCountry))
                {
                    strSql += " and Country like '%";
                    strSql += strCountry;
                    strSql += "%'";
                }

                if (!IsEmpty(strStatus))
                {
                    strSql += " and Status like '%";
                    strSql += strStatus;
                    strSql += "%'";
                }

                if (!IsEmpty(strApplicant))
                {
                    strSql += " and Applicant like '%";
                    strSql += strApplicant;
                    strSql += "%'";
                }

                if (!IsEmpty(strInventor))
                {
                    strSql += " and Inventor like '%";
                    strSql += strInventor;
                    strSql += "%'";
                }

                if (!IsEmpty(strResponsiblePerson))
                {
                    strSql += " and ProjectCode in (select ProjectCode from ProjectSupervisor where ResponsiblePerson like'%";
                    strSql += strResponsiblePerson;
                    strSql += "%')";
                }

                if (!IsEmpty(strAgent))
                {
                    strSql += " and Agent like '%";
                    strSql += strAgent;
                    strSql += "%'";
                }

                if (!IsEmpty(strClient))
                {
                    strSql += " and Client like '%";
                    strSql += strClient;
                    strSql += "%'";
                }

                if (!IsEmpty(strProjectClass))
                {
                    strSql += " and ProjectClass like '%";
                    strSql += strProjectClass;
                    strSql += "%'";
                }

                if (!IsEmpty(strDueDateFrom))
                {
                    strSql += " and (LastNextDueDate is not null) and convert(varchar(10), LastNextDueDate) >= '";
                    strSql += strDueDateFrom;
                    strSql += "'";
                }

                if (!IsEmpty(strDueDateTo))
                {
                    strSql += " and (LastNextDueDate is not null) and convert(varchar(10), LastNextDueDate) <= '";
                    strSql += strDueDateTo;
                    strSql += "'";
                }

                if (bIsDelete)
                {
                    strSql += " and IsDelete = 1";
                }
                else
                {
                    strSql += " and IsDelete = 0";
                }

                strSql += " and (1 in (select GroupId from DBF_USER where LoginName = '";
                strSql += strUserID;
                strSql += "') or 1 in (select UserRole from DBF_USER where LoginName = '";
                strSql += strUserID;
                strSql += "') or OwnerGroupId in (select GroupId from DBF_USER where LoginName = '";
                strSql += strUserID;
                strSql += "'))";

                m_strMessage = strSql;
                return m_objDB.getDataTable(strSql, "Project");
            }

            return null;
        }

        public DataRow GetItem(string strProjectCode)
        {
            return null;
        }

        public int Delete(string strProjectCode, string strUserID, string strIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "Update Project set IsDelete = 1, DeleteBy = '";
                strSql += strUserID;
                strSql += "', DeleteIP = '";
                strSql += strIP;
                strSql += "', DeleteDate = getDate()";
                strSql += " where ProjectCode = '";
                strSql += strProjectCode;
                strSql += "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.ErrCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Restore(string strProjectCode, string strUserID, string strIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "Update Project set IsDelete = 0, UpdateBy = '";
                strSql += strUserID;
                strSql += "', UpdateIP = '";
                strSql += strIP;
                strSql += "', UpdateDate = getDate()";
                strSql += " where ProjectCode = '";
                strSql += strProjectCode;
                strSql += "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.ErrCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public DataTable GetResponsiblePerson(string strProjectCode)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "select p.*, UserName from ProjectSupervisor p, dbf_user u where ResponsiblePerson = LoginName and p.IsDelete = 0 and ProjectCode = '";
                strSql += strProjectCode;
                strSql += "'";
                return m_objDB.getDataTable(strSql, "ProjectSupervisor");
            }

            return null;
        }

        public DataRow GetResponsiblePerson(string strProjectCode, string strUserID)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "select * from ProjectSupervisor where ProjectCode = '";
                strSql += strProjectCode;
                strSql += "' and ResponsiblePerson = '";
                strSql += strUserID;
                strSql += "'";
                return m_objDB.getDataRow(strSql, "ProjectSupervisor");
            }

            return null;
        }

        public bool IsResponsiblePersonOwner(string strProjectCode, string strUserID)
        {
            if (ConnectDB())
            {
                string strSql = "select * from Project where ProjectCode = '";
                strSql += strProjectCode;
                strSql += "' and CreateBy = '";
                strSql += strUserID;
                strSql += "'";

                if (m_objDB.getDataRow(strSql, "ProjectSupervisor") == null)
                {
                    return GetResponsiblePerson(strProjectCode, strUserID) != null;
                }
            }

            return false;
        }

        public int InsertResponsiblePerson(string strProjectCode, string strResponsiblePerson, string strOwnerGroupID, string strUserID, string strUserIP)
        {
            if (ConnectDB())
            {
                DataRow dr = GetResponsiblePerson(strProjectCode, strResponsiblePerson);

                CDBError objError = new CDBError();
                string strSql = "";

                if (dr == null)
                {
                    strSql = "insert into ProjectSupervisor(ProjectCode, ResponsiblePerson, OwnerGroupID, CreateBy, CreateIP, CreateDate, UpdateBy, UpdateIP, UpdateDate) values ('";
                    strSql += strProjectCode;
                    strSql += "', '";
                    strSql += strResponsiblePerson;
                    strSql += "', ";
                    strSql += strOwnerGroupID;
                    strSql += ", '";
                    strSql += strUserID;
                    strSql += "', '";
                    strSql += strUserIP;
                    strSql += "', getDate(), '";
                    strSql += strUserID;
                    strSql += "', '";
                    strSql += strUserIP;
                    strSql += "', getDate())";
                }
                else
                {
                    strSql = "update ProjectSupervisor set IsDelete = 0, UpdateBy = '";
                    strSql += strUserID;
                    strSql += "', UpdateIP = '";
                    strSql += strUserIP;
                    strSql += "', UpdateDate = getDate()";
                    strSql += " where ProjectCode = '";
                    strSql += strProjectCode;
                    strSql += "' and ResponsiblePerson = '";
                    strSql += strResponsiblePerson;
                    strSql += "'";
                }

                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.ErrCode;
            }

            return -1;
        }

        public int DeleteResponsiblePerson(string strProjectCode, string strResponsiblePerson, string strUserID, string strUserIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "update ProjectSupervisor set IsDelete = 1, DeleteBy = '";
                strSql += strUserID;
                strSql += "', DeleteIP = '";
                strSql += strUserIP;
                strSql += "', DeleteDate = getDate()";
                strSql += " where ProjectCode = '";
                strSql += strProjectCode;
                strSql += "' and ResponsiblePerson = '";
                strSql += strResponsiblePerson;
                strSql += "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.ErrCode;
            }

            return -1;
        }

        public DataTable GetProjectProduct(string strProjectCode)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "select * from ProductProject where IsDelete = 0 and ProjectCode = '";
                strSql += strProjectCode;
                strSql += "'";
                return m_objDB.getDataTable(strSql, "ProductProject");
            }

            return null;
        }
    }
}
